USE MusicLine

GO

Create Table UserAccounts
(
	UserLogonId varchar(50) Primary Key Not Null,
	UserPassword varchar(50) Not Null,
	InvalidLogonAttempts int Not Null Default(0)
)

GO
    
Insert Into UserAccounts values('admin','secret',0)

GO

Create Procedure ValidateLogonCredentials(@UserLogonId varchar(50), @UserPassword varchar(50))
As

Set NoCount On

If Exists(Select * From UserAccounts Where UserLogonId=@UserLogonId And UserPassword=@UserPassword And InvalidLogonAttempts < 5)
Select 1 As "ValidLogon"
Else
Begin
Update UserAccounts
    Set InvalidLogonAttempts = InvalidLogonAttempts + 1
    Where UserLogonId = @UserLogonId
Select 0 As "ValidLogon"
End

Return @@Error
					
GO


insert into UserAccounts (UserLogonId, UserPassword, InvalidLogonAttempts)
values('barack', 'barack', 0);

GO

insert into UserAccounts (UserLogonId, UserPassword, InvalidLogonAttempts)
values('john', 'john', 0); 

GO
 
create table Roles
(
	RoleName varchar(50)  primary key not null,
	RoleDescription varchar(256)
);

GO

insert into Roles (RoleName, RoleDescription)
values('admin', 'All Privileges');

GO

insert into Roles (RoleName, RoleDescription)
values('blogger', 'Can Blog');

GO

insert into Roles (RoleName, RoleDescription)
values('event_creator', 'Can Create New Events');


GO

create table UserAccounts_Roles
(
    UserLogonId varchar(50) not null Constraint UserAccounts_Roles_UserLogonId Foreign Key References UserAccounts(UserLogonId),
    RoleName varchar(50) not null Constraint UserAccounts_Roles_RoleName Foreign Key References Roles(RoleName),
    primary key(UserLogonId, RoleName)
);

GO

insert into UserAccounts_Roles (UserLogonId, RoleName)
values('barack', 'admin');

insert into UserAccounts_Roles (UserLogonId, RoleName)

GO
values('john', 'blogger');

insert into UserAccounts_Roles (UserLogonId, RoleName)

GO

values('john', 'event_creator');


Go		
CREATE Procedure GetUserRoles (@UserLogonId varchar(50))
As
	select RoleName
	from UserAccounts_Roles
	where UserLogonId = @UserLogonId;


					